Connecting to Google BigQuery involves first providing a mechanism to authenticate to BigQuery using one of three methods. Once authenticated, users need to select the relevant project to enable the connection.
- First, go to the security tab and setup BigQuery Authentication (see below).
- Next, go to the connection tab and setup the Project selections.
- Click here for more details on setting BigQuery as a data source.
Security and Authentication
On the security tab, select the authentication method:
- Service Account - via JSON file
- SSO specific user - via interactive authentication to Google with a single account for all users
- SSO end user - via interactive authentication to Google by each INDIVIDUAL user as the login into Pyramid
Service Account OAuth Connections
When using a service account, the authentication is common to all users of the connection. The details of the authentication are contained in a JSON file downloaded from the BigQuery administrative console. It contains details like details like Client ID and client secret.
User Account OAuth Connections
Another way to connect to BigQuery is via SSO OAuth Authentication. This type of authentication utilizes the user's credentials to connect and authenticate access to a given data source. The process is often used in big organizations that have centralized security and are using one framework to secure all data assets.
Authentication Options for Google BigQuery and Google Analytics
When creating a data source in Pyramid and completing the setup 'card', administrators can elect which type of authentication model to use:
- Single Sign On (OAuth) – Specific User: All users of this data source will share and use the credentials and sign in code defined here.
- Single Sign On (OAuth) – End User: Each user will be prompted to sign into Google when starting Pyramid or when connecting to the data source. This is a “one off” event. The user’s sign in code will be stored and reused for subsequent data access. Pyramid will automatically refresh this as needed. All users will share the Client ID and Client Secret defined here.
Enabling End User OAuth Authentication
Google BigQuery requires the creation of Client ID and Client Secret strings that provide in an encoded manner, the information needed to connect to the particular BigQuery data required. These strings are generated through the Google BigQuery management console and copied and pasted to the relevant dialogue boxes on the data card.
Once logged into Google BigQuery, Client ID and Client Secret strings can be generated from the “Get Credentials” button on this page.
- Client ID: Google BigQuery Client ID
- Client Secret: Google BigQuery Client Secret
The Client ID and Client Secret used by all users to access the Google BigQuery application. With the drop down set to “Single Sign On (OAuth) – End User”, each user will be prompted to sign into Google for individually authenticated data access.
With the drop down set to “Single Sign On (OAuth) – Specific User”, each user will share the Google account as well as the Client ID and Client Secret.
- Sign-In with Google: Use this button to sign into Google to retrieve the Refresh Code
- Refresh Code: Returned by Google and used by Pyramid to connect to the Google Account.
Project Selection
After the authentication has been configured, return to the connection tab and select the relevant BigQuery Project from the drop down. optionally, also select the Quota Project from the second drop down.